<?php
class Close extends Admin_Controller
{
	
	function __construct()
	{
		parent::__construct();
		$this->load->model('fund_model','fund');
		$this->load->model('voucher_model','voucher');
		$this->load->model('voucher_balance_model','balance');
		$this->load->model('voucher_check_model','check');
		$this->load->model('voucher_receive_paid_model','receive_paid');
		$this->load->model('voucher_detail_model','voucher_detail');
	}
	
	function index($fund_id)
	{
		$user = login_data();
		$data['fund'] = $this->fund->get_row($fund_id);
		$agency_id = (level_data('view'))?(isset($_GET['agency_id']))?$_GET['agency_id']:$user['agency_id']:$user['agency_id'];		
		$data['agency_id'] = $agency_id;	
		list($year,$month,$data['year'],$data['month']) = last_voucher_date($fund_id);
		$where = '';
		$where .= ' and mode in (\'closed\',\'depreciation\')';
		if(!empty($agency_id))$where .= ' and vouchers.agency_id = '.$agency_id;
		list($start_date,$end_date) = year_budget($data['year']);
		$where .= ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\'';
		
		//$this->db->debug = TRUE;
		$data['result'] = $this->voucher->where('vouchers.fund_id = '.$fund_id.$where)->sort('voucher_date')->order('desc')->get();
		//$this->db->debug = FALSE;
		$data['paginaion'] = $this->voucher->pagination();
		$this->template->build('voucher_close_index',$data);
	}
	
	function view($fund_id)
	{
		$data['low_height'] = $this->db->getOne('select id from accounts where group_id = 8 and fund_id = '.$fund_id);
		$data['low_height_total']  = $this->db->getOne('select id from accounts where group_id = 9 and fund_id = '.$fund_id);
		
		$user = login_data();
		$data['fund'] = $this->fund->get_row($fund_id);
		$agency_id = (level_data('view'))?(isset($_GET['agency_id']))?$_GET['agency_id']:$user['agency_id']:$user['agency_id'];		
		$data['agency_id'] = $agency_id;	
		list($year,$month,$data['year'],$data['month']) = last_voucher_date($fund_id);

		$user_id = $user['id'];
		list($start_date,$end_date) = year_budget($data['year']);
		$where = ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\'';
		$where .= ' and vouchers.fund_id = '.$fund_id.' and vouchers.agency_id = '.$agency_id;
		$data['earning'] = $this->voucher_detail->get('
		select id,name,category_id,cr_sum price
		from (
		select accounts.id,accounts.name,accounts.category_id,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then 0 else (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) dr_sum,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else 0 end) cr_sum
		 from accounts
		join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) a2
		on a2.account_id = accounts.id
		join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) b2
		on b2.account_id = accounts.id
		where accounts.category_id in (4,5)
		and accounts.fund_id = '.$fund_id.'
		) a
		where a.cr_sum > 0
		',FALSE);
		
		
			
		$data['cost'] = $this->voucher_detail->get('
		select id,name,category_id,dr_sum price
		from (
		select accounts.id,accounts.name,accounts.category_id,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then 0 else (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) dr_sum,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else 0 end) cr_sum
		 from accounts
		join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) a2
		on a2.account_id = accounts.id
		join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) b2
		on b2.account_id = accounts.id
		where accounts.category_id in (4,5)
		and accounts.fund_id = '.$fund_id.'
		) a
		where a.dr_sum > 0
		',FALSE);
			
		$sum_earning = $this->db->getOne('
		select sum(cr_sum) price
		from (
		select accounts.id,accounts.name,accounts.category_id,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then 0 else (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) dr_sum,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else 0 end) cr_sum
		 from accounts
		join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) a2
		on a2.account_id = accounts.id
		join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) b2
		on b2.account_id = accounts.id
		where accounts.category_id in (4,5)
		and accounts.fund_id = '.$fund_id.'
		) a
		where a.cr_sum > 0
		',FALSE);
			
		$sum_cost = $this->db->getOne('
		select sum(dr_sum) price
		from (
		select accounts.id,accounts.name,accounts.category_id,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then 0 else (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) dr_sum,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else 0 end) cr_sum
		 from accounts
		join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) a2
		on a2.account_id = accounts.id
		join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) b2
		on b2.account_id = accounts.id
		where accounts.category_id in (4,5)
		and accounts.fund_id = '.$fund_id.'
		) a
		where a.dr_sum > 0
		',FALSE);
		$sum = $sum_earning - $sum_cost;

		$data['result'] = $sum;
	
		$voucher['voucher_type_id'] = 3;
		$voucher['user_id'] = $user_id;
		$voucher['fund_id'] = $fund_id;
		$voucher['voucher_date'] = $end_date;
		$voucher['agency_id'] = $agency_id;
		$voucher['mode'] = 'closed';
		$voucher['approve_name'] = $approve_name;
		$voucher['description'] = 'ปิดบัญชีรายได้และค่าใช้จ่ายเข้าบัญชีรายได้สูง/(ต่ำ) กว่าค่าใช้จ่าย';
		//$balance_id = $this->voucher->save($voucher);
		
		$data['voucher1'] = $voucher;
		
		
		$voucher2['voucher_type_id'] = 3;
		$voucher2['user_id'] = $user_id;
		$voucher2['fund_id'] = $fund_id;
		$voucher2['voucher_date'] = $end_date;
		$voucher2['agency_id'] = $agency_id;
		$voucher2['mode'] = 'depreciation';
		$voucher2['approve_name'] = $approve_name;
		$voucher2['description'] = 'ปิดบัญชีรายได้สูง/(ต่ำ) กว่าค่าใช้จ่ายเข้าบัญชีรายได้สูง/(ต่ำ)กว่าค่าใช้จ่ายสะสม';
		$data['voucher2'] = $voucher2;
			
		
		
		$this->template->build('voucher_close',$data);
	}
	
	function save($fund_id)
	{
		$low_height = $this->db->getOne('select id from accounts where group_id = 8 and fund_id = '.$fund_id);
		$low_height_total  = $this->db->getOne('select id from accounts where group_id = 9 and fund_id = '.$fund_id);
		$user = login_data();
		$fund_id = $fund_id;
		$agency_id = $user['agency_id'];
		$user_id = $user['id'];
		list($year,$month,$data['year'],$data['month']) = last_voucher_date($fund_id);
		list($start_date,$end_date) = year_budget($data['year']);
		$where = ' and vouchers.voucher_date between \''.$start_date.'\' and \''.$end_date.'\'';
		$where .= ' and vouchers.fund_id = '.$fund_id.' and vouchers.agency_id = '.$agency_id;
			
		$data['earning'] = $this->voucher_detail->get('
		select id,name,category_id,cr_sum price
		from (
		select accounts.id,accounts.name,accounts.category_id,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then 0 else (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) dr_sum,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else 0 end) cr_sum
		 from accounts
		join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) a2
		on a2.account_id = accounts.id
		join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) b2
		on b2.account_id = accounts.id
		where accounts.category_id in (4,5)
		and accounts.fund_id = '.$fund_id.'
		) a
		where a.cr_sum > 0
		',FALSE);
		
		
			
		$data['cost'] = $this->voucher_detail->get('
		select id,name,category_id,dr_sum price
		from (
		select accounts.id,accounts.name,accounts.category_id,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then 0 else (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) dr_sum,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else 0 end) cr_sum
		 from accounts
		join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) a2
		on a2.account_id = accounts.id
		join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) b2
		on b2.account_id = accounts.id
		where accounts.category_id in (4,5)
		and accounts.fund_id = '.$fund_id.'
		) a
		where a.dr_sum > 0
		',FALSE);
			
		$sum_earning = $this->db->getOne('
		select sum(cr_sum) price
		from (
		select accounts.id,accounts.name,accounts.category_id,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then 0 else (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) dr_sum,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else 0 end) cr_sum
		 from accounts
		join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) a2
		on a2.account_id = accounts.id
		join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) b2
		on b2.account_id = accounts.id
		where accounts.category_id in (4,5)
		and accounts.fund_id = '.$fund_id.'
		) a
		where a.cr_sum > 0
		',FALSE);
			
		$sum_cost = $this->db->getOne('
		select sum(dr_sum) price
		from (
		select accounts.id,accounts.name,accounts.category_id,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then 0 else (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) end) dr_sum,
		(CASE when (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) < 0 then (COALESCE(a2.dr_month,0) - COALESCE(b2.cr_month,0)) * -1 else 0 end) cr_sum
		 from accounts
		join (select account_id,sum(price) dr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'dr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) a2
		on a2.account_id = accounts.id
		join (select account_id,sum(price) cr_month from voucher_details join vouchers on voucher_details.voucher_id=vouchers.id where "TYPE" = \'cr\' and voucher_id in (select id from vouchers where is_balance is null and (mode is null or mode = \'edit\')) '.$where.' group by account_id) b2
		on b2.account_id = accounts.id
		where accounts.category_id in (4,5)
		and accounts.fund_id = '.$fund_id.'
		) a
		where a.dr_sum > 0
		',FALSE);
			
		$sum = $sum_earning - $sum_cost;

		$voucher['voucher_type_id'] = 3;
		$voucher['user_id'] = $user_id;
		$voucher['fund_id'] = $fund_id;
		$voucher['voucher_date'] = $end_date;
		$voucher['agency_id'] = $agency_id;
		$voucher['mode'] = 'closed';
		$voucher['approve_name'] = $approve_name;
		$voucher['description'] = 'ปิดบัญชีรายได้และค่าใช้จ่ายเข้าบัญชีรายได้สูง/(ต่ำ) กว่าค่าใช้จ่าย';
		
		$approve = $this->db->getRow('select * from approves where fund_id = '.$fund_id.' and agency_id = '.$agency_id);
		dbConvert($approve);
		$voucher['examiner'] = $approve['examiner'];
		$voucher['examiner_position'] = $approve['examiner_position'];
		$voucher['endorsee'] = $approve['endorsee'];
		$voucher['endorsee_position'] = $approve['endorsee_position'];
		
		$balance_id = $this->voucher->save($voucher);
		foreach($data['earning'] as $dr)
		{
			$detail['account_id'] = $dr['id'];
			$detail['price'] = $dr['price'];
			$detail['type'] = 'dr';
			$detail['voucher_id'] = $balance_id;
			$this->voucher_detail->save($detail);
			unset($detail);
		}
		if($sum < 0)
		{
			$detail['account_id'] = $low_height;
			$detail['price'] = $sum * -1;
			$detail['type'] = 'dr';
			$detail['voucher_id'] = $balance_id;
			$this->voucher_detail->save($detail);
			unset($detail);
		}
		foreach($data['cost'] as $cr)
		{
			$detail['account_id'] = $cr['id'];
			$detail['price'] = $cr['price'];
			$detail['type'] = 'cr';
			$detail['voucher_id'] = $balance_id;
			$this->voucher_detail->save($detail);
			unset($detail);
		}
		if($sum > 0)
		{
			$detail['account_id'] = $low_height;
			$detail['price'] = $sum;
			$detail['type'] = 'cr';
			$detail['voucher_id'] = $balance_id;
			$this->voucher_detail->save($detail);
			unset($detail);
		}
		if($sum <> 0)
		{
			$voucher['voucher_type_id'] = 3;
			$voucher['user_id'] = $user_id;
			$voucher['fund_id'] = $fund_id;
			$voucher['voucher_date'] = $end_date;
			$voucher['agency_id'] = $agency_id;
			$voucher['mode'] = 'depreciation';
			$voucher['approve_name'] = $approve_name;
			$voucher['description'] = 'ปิดบัญชีรายได้สูง/(ต่ำ) กว่าค่าใช้จ่ายเข้าบัญชีรายได้สูง/(ต่ำ)กว่าค่าใช้จ่ายสะสม';
			$voucher['examiner'] = $approve['examiner'];
			$voucher['examiner_position'] = $approve['examiner_position'];
			$voucher['endorsee'] = $approve['endorsee'];
			$voucher['endorsee_position'] = $approve['endorsee_position'];
			$balance_id = $this->voucher->save($voucher);
			if($sum > 0)
			{
				$detail['account_id'] = $low_height;
				$detail['price'] = $sum;
				$detail['type'] = 'dr';
				$detail['voucher_id'] = $balance_id;
				$this->voucher_detail->save($detail);
				unset($detail);
					
				$detail['account_id'] = $low_height_total;
				$detail['price'] = $sum;
				$detail['type'] = 'cr';
				$detail['voucher_id'] = $balance_id;
				$this->voucher_detail->save($detail);
				unset($detail);
			}
			if($sum < 0)
			{
				$detail['account_id'] = $low_height_total;
				$detail['price'] = $sum * -1;
				$detail['type'] = 'dr';
				$detail['voucher_id'] = $balance_id;
				$this->voucher_detail->save($detail);
				unset($detail);
					
				$detail['account_id'] = $low_height;
				$detail['price'] = $sum * -1;
				$detail['type'] = 'cr';
				$detail['voucher_id'] = $balance_id;
				$this->voucher_detail->save($detail);
				unset($detail);
			}
		}
		
		unset($voucher);
		$prev_balances = $this->voucher->get_close_balance($fund_id,$user['agency_id'],$year,$month);
		pre_dump($prev_balances);
		if($prev_balances)
		{
			$voucher['description'] = 'ยอดยกมาประจำเดือน '.month($month + 1).' ปี '.($year + 543);
			$voucher['voucher_type_id'] = 3;
			$voucher['user_id'] = $this->session->userdata('id');
			$voucher['fund_id'] = $fund_id;
			$voucher['is_balance'] = 1;
			$voucher['approve_name'] = $approve_name;
			$voucher['voucher_date'] = $year.'-'.($month + 1).'-1';
			$voucher['agency_id'] = $user['agency_id'];
			$balance_id = $this->voucher->save($voucher);
			foreach($prev_balances as $balance)
			{
				if(($balance['dr_sum']<>"0.00")||($balance['cr_sum']<>"0.00"))
				{
					$detail['account_id'] = $balance['id'];
					$detail['price'] = ($balance['dr_sum']<>"0.00")?$balance['dr_sum']:$balance['cr_sum'];
					$detail['type'] = ($balance['dr_sum']<>"0.00")?'dr':'cr';
					$detail['voucher_id'] = $balance_id;
					$this->voucher_detail->save($detail);
				}
					unset($detail);
			}
		}
		
		redirect('voucher/close/index/'.$fund_id);
	}
	
}
?>